package srdb.migration.Util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;

import srdb.migration.common.MTKConstants.DATABASE_TYPE;

public class Util implements Constants {

    public static String[] getAllSchemas(DBInfo dbInfo) {
        List<String> schemas = new ArrayList<String>();
        Connection conn = null;
        try {
            conn = getConnection(dbInfo);
            ResultSet rset = conn.getMetaData().getSchemas();
            while (rset.next()) {
                schemas.add(rset.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return schemas.toArray(new String[schemas.size()]);
    }

    public static boolean testConnection(DBInfo dbInfo) throws SQLException {
        Connection conn = getConnection(dbInfo);
        if (conn == null) {
            return false;
        }
        conn.close();
        return true;
    }

    public static String getConnectionURL(DBInfo dbInfo) {
        DATABASE_TYPE dbType = dbInfo.getType();
        if (dbType == DATABASE_TYPE.ORACLE) {
            return MessageFormat.format(TEMPLATE_CON_URL_ORACLE,
                    dbInfo.getHostIP(), dbInfo.getPort(), dbInfo.getSid());
        } else if (dbType == DATABASE_TYPE.MYSQL) {
            return MessageFormat.format(TEMPLATE_CON_URL_MYSQL,
                    dbInfo.getHostIP(), dbInfo.getPort(), dbInfo.getDatabase());
        } else if (dbType == DATABASE_TYPE.SQLSERVER) {
            return MessageFormat.format(TEMPLATE_CON_URL_SQLSERVER,
                    dbInfo.getHostIP(), dbInfo.getPort(), dbInfo.getDatabase());
        } else if (dbType == DATABASE_TYPE.DB2) {
            return MessageFormat.format(TEMPLATE_CON_URL_DB2,
                    dbInfo.getHostIP(), dbInfo.getPort(), dbInfo.getDatabase());
        } else if (dbType == DATABASE_TYPE.SRDB) {
            return MessageFormat.format(TEMPLATE_CON_URL_SRDB,
                    dbInfo.getHostIP(), dbInfo.getPort(), dbInfo.getDatabase());
        }
        return "";
    }

    public static Table[] getAllTables(DBInfo dbInfo, String schema) {
        List<Table> tables = new ArrayList<Table>();
        Connection conn = null;
        try {
            conn = getConnection(dbInfo);
            String[] types = new String[]{"TABLE"};
            ResultSet rset = conn.getMetaData().getTables("", schema, null,
                    types);
            while (rset.next()) {
                Table table = new Table();
                table.setName(rset.getString(COLUMN_TABLE_NAME));
                tables.add(table);
            }
            DATABASE_TYPE dbType = dbInfo.getType();
            for (Table table : tables) {
                table.setPartition(isPartition(dbType, conn, table.getName()));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return tables.toArray(new Table[tables.size()]);
    }

    public static int[] parseDataCount(DBInfo dbInfo, String schema) {
        Connection conn = null;
        try {
            conn = getConnection(dbInfo);
            String[] tables = getAllTableNames(conn, schema);
            return parseDataCount(conn, schema, tables);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return getParseDataCountExceptionResult();
    }

    public static int[] parseDataCount(DBInfo dbInfo, String schema,
                                       String[] tables) {
        Connection conn = null;
        try {
            conn = getConnection(dbInfo);
            return parseDataCount(conn, schema, tables);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return getParseDataCountExceptionResult();
    }

    private static int[] getParseDataCountExceptionResult() {
        int[] exceptionResult = new int[PARSE_DATA_ARRAY_LENGTH];
        for (int i = 0; i < exceptionResult.length; i++) {
            exceptionResult[i] = -1;
        }
        return exceptionResult;
    }

    private static int[] parseDataCount(Connection conn, String schema,
                                        String[] tables) throws SQLException {
        int[] result = new int[PARSE_DATA_ARRAY_LENGTH];
        result[INDEX_TABLE_COUNT_TOTAL] = tables.length;
        result[INDEX_ROW_COUNT_TOTAL] = 0;
        result[INDEX_TABLE_COUNT_TENMILLION_ABOVE] = 0;
        result[INDEX_TABLE_COUNT_BETWEEN_MILLION_TENMILLION] = 0;
        result[INDEX_TABLE_COUNT_MILLION_BELOW] = 0;
        for (String table : tables) {
            Statement stmt = conn.createStatement();
            ResultSet rset = stmt.executeQuery(MessageFormat.format(
                    TEMPLATE_SQL_ROW_TOTAL, schema, table));
            if (rset.next()) {
                int rowCount = rset.getInt(1);
                result[INDEX_ROW_COUNT_TOTAL] += rowCount;
                if (rowCount >= NUM_TEN_MILLION) {
                    result[INDEX_TABLE_COUNT_TENMILLION_ABOVE]++;
                } else if (rowCount >= NUM_MILLION) {
                    result[INDEX_TABLE_COUNT_BETWEEN_MILLION_TENMILLION]++;
                } else {
                    result[INDEX_TABLE_COUNT_MILLION_BELOW]++;
                }
            }
            stmt.close();
        }
        return result;
    }

    private static Connection getConnection(DBInfo dbInfo) throws SQLException {
        try {
            Class.forName(getDriverClassName(dbInfo.getType()));
        } catch (ClassNotFoundException e) {
            throw new SQLException(e);
        }
        return DriverManager.getConnection(getConnectionURL(dbInfo),
                dbInfo.getUserName(), dbInfo.getPassword());
    }

    private static String getDriverClassName(DATABASE_TYPE dbType) {
        if (dbType == DATABASE_TYPE.ORACLE) {
            return DRIVER_NAME_ORACLE;
        } else if (dbType == DATABASE_TYPE.MYSQL) {
            return DRIVER_NAME_MYSQL;
        } else if (dbType == DATABASE_TYPE.SQLSERVER) {
            return DRIVER_NAME_SQLSERVER;
        } else if (dbType == DATABASE_TYPE.DB2) {
            return DRIVER_NAME_DB2;
        } else if (dbType == DATABASE_TYPE.SRDB) {
            return DRIVER_NAME_SRDB;
        }
        return "";
    }

    private static boolean isPartition(DATABASE_TYPE dbType, Connection conn,
                                       String tableName) throws SQLException {
        if (tableName == null) {
            return false;
        }
        if (dbType == DATABASE_TYPE.ORACLE) {
            Statement stmt = conn.createStatement();
            ResultSet rset = stmt.executeQuery(MessageFormat.format(
                    TEMPLATE_SQL_PARTITION_ORACLE, tableName));
            if (rset.next()) {
                stmt.close();
                return true;
            }
            stmt.close();
            return false;
        } else if (dbType == DATABASE_TYPE.MYSQL) {
        } else if (dbType == DATABASE_TYPE.SQLSERVER) {
        } else if (dbType == DATABASE_TYPE.DB2) {
        } else if (dbType == DATABASE_TYPE.SRDB) {
            Statement stmt = conn.createStatement();
            ResultSet rset = stmt.executeQuery(MessageFormat.format(
                    TEMPLATE_SQL_PARTITION_SRDB, tableName));
            if (rset.next()) {
                if (rset.getBoolean(1) || rset.getBoolean(2)) {
                    stmt.close();
                    return true;
                }
            }
            stmt.close();
            return false;
        }
        return false;
    }

    private static String[] getAllTableNames(Connection conn, String schema)
            throws SQLException {
        List<String> tableNames = new ArrayList<String>();
        String[] types = new String[]{"TABLE"};
        ResultSet rset = conn.getMetaData().getTables("", schema, null, types);
        while (rset.next()) {
            tableNames.add(rset.getString(COLUMN_TABLE_NAME));
        }
        return tableNames.toArray(new String[tableNames.size()]);
    }

    private Util() {
    }


    /**
     * 获取迁移操作对象
     *
     * @param migration
     * @return 迁移操作对象, 供调用迁移
     */
    public static IMigrationOperation getMigrationOperation(Migration migration) {
        return new MigrationOperation(migration);
    }

    /**
     * test
     *
     * @param args
     */
    public static void main(String[] args) {
//        String[] params = "-dropSchema true -tables EMP,DEPT -cpBatchSize 32 -targetSchema simplelove scott".split(" ");
        //
        DBInfo srcDb = new DBInfo();
        srcDb.setType(DATABASE_TYPE.ORACLE);
        srcDb.setPassword("123456");
        srcDb.setUserName("system");
        srcDb.setHostIP("10.168.250.42");
        srcDb.setPort("1521");
        srcDb.setSid("ora11");
        //
        DBInfo targetDb = new DBInfo();
        targetDb.setType(DATABASE_TYPE.SRDB);
        targetDb.setPassword("dba");
        targetDb.setUserName("dba");
        targetDb.setHostIP("10.168.250.31");
        targetDb.setPort("1975");
        targetDb.setDatabase("migration");
        //
        Migration migration = new Migration();
        migration.setSrcDBInfo(srcDb);
        migration.setDstDBInfo(targetDb);
        migration.setMigMode(4);
        migration.setSchema("scott");
//        migration.setIuiOperation(new UIOperation());
        migration.setDropSchema(true);
        migration.setCpBatchSize(32);
//        migration.setMigMode(5);
        String[] tables = {"EMP","DEPT","BONUS","SALGRADE"};
        migration.setTables(tables);
        migration.setTruncLoad(true);
        final IMigrationOperation im = Util.getMigrationOperation(migration);
//        new Thread(new Runnable() {
//            @Override
//            public void run() {
//                ArrayList<Double> list = new ArrayList<Double>();
//                long time = System.currentTimeMillis();
//                System.out.println("**********************Util:"+MigrationToolkit.mmFlag);
//                while (!MigrationToolkit.mmFlag) {
//                    if (System.currentTimeMillis() - time > 10) {
//                        list.add(im.getProgress());
//                        time = System.currentTimeMillis();
//                    }
//                }
//                System.out.println(list);
//            }
//        }).start();
        im.execute();
//        MigrationToolkit.executeMigration(params, srcDb, targetDb);
    }

//    private static class UIOperation implements IUIOperation {
//        @Override
//        public void infoProgress(double migrationPer) {
//
//        }
//
//        @Override
//        public void pushLogs(String log) {
//            System.out.println("&&&&&&&&%%%%%%%%%********" + log);
//        }
//    }
}
